The present invention relates generally to computer-implemented data processing methods, systems, and computer program products. More particularly, it relates to dynamically optimizing database queries, as appropriate, so that query runtimes complete within query governing time boundaries.
In typical database querying using SQL, query governors are mechanisms that perform several functions. Typically, query governors may be configured to allow all queries to run a predefined maximum amount of time, for example several seconds for all queries for all connections, or just the queries for a specific connection. One specific query governor function is to preempt or prevent long-running queries from executing before they start. This preemption is based on determinations of whether estimates of query runtime, as made by a query optimizer, will exceed predefined query time boundaries that have been set by the query governor. If a determination is made that the threshold may be exceeded then a warning may be issued and the query does not run. This is beneficial since it prevents system resources from being needlessly consumed.
While query governors manage queries in the foregoing fashion, there are no known approaches for dynamically predicting the appropriate amount of computer resources needed for fitting otherwise long-running queries within predefined query time boundaries so that the queries are not preempted from running. Furthermore, no approaches are known for dynamically allocating the appropriate amount of computer resources to fit the otherwise long-running queries within the predefined query time boundaries wherein the allocations are based on the dynamic predictions. In addition, no approaches are known for use in systems wherein the allocated resources are made available from logically partitioned and/or grid environments for achieving the foregoing tasks. Moreover, no approaches are known for apportioning costs or fees to customers based on computer resources actually utilized to achieve the foregoing, let alone to satisfy the configurable costs and performance demands of customers.
Accordingly there are needs for methods, systems, and computer program products for the benefit of providing query governors that: manage data by preventing long-running queries from executing; dynamically predict the appropriate amount of computer resources needed for fitting query runs so as to complete within predefined time boundaries; dynamically allocate the appropriate amount of computer resources for making query runs complete within the predefined query time boundaries based on such dynamic predictions; are applicable in logically partitioned and/or grid environments for apportioning resources; and, apportion costs to customers based on actually utilized computer resources, whereby configurable demands of customers are satisfied.
Accordingly, without such needs being satisfied, the true potential of running queries with query governors will not be easily achieved.